# == Schema Information
# Schema version: 37
#
# Table name: submissions
#
#  id               :integer(11)   not null, primary key
#  created_at       :datetime      
#  updated_at       :datetime      
#  user_id          :integer(11)   
#  cycle_id         :integer(11)   
#  data             :text          
#  status_id        :integer(11)   
#  submitted_at     :datetime      
#  total_cost       :float         default(-1.0)
#  manager_id       :integer(11)   
#  judges_avg_score :float         default(0.0)
#  granted          :boolean(1)    
#

class Submission < ActiveRecord::Base 
  belongs_to :status
  belongs_to :cycle
  belongs_to :user 
  has_many :approvals, :dependent => :destroy
  has_many :judgments, :dependent => :destroy
  belongs_to :manager
                
  validates_presence_of :user, :cycle, :status
  validates_uniqueness_of :cycle_id, :scope => "user_id", :message => "You can only apply for this grant once."                      
  validates_numericality_of :total_cost
  
  
  def self.mark_completed

    conn = ActiveRecord::Base.connection()

    # find all of the submissions that are ready to be moved to completed.  If
    # the status is judging and all of the judgement are complete, then they
    # are ready.
    # mysql only!!
    sql = """SELECT s.id
               FROM submissions s
               JOIN statuses ON (s.status_id = statuses.id)
               LEFT JOIN (SELECT submission_id as id, count(*) as num 
                            FROM judgments 
                           WHERE complete = 1 
                           GROUP BY submission_id) jcc ON (jcc.id = s.id)
               LEFT JOIN (SELECT submission_id as id, count(*) as num 
                            FROM judgments 
                           GROUP BY submission_id) jc ON (jc.id = s.id)
              WHERE statuses.name = 'Judging'
                AND jcc.num = jc.num"""

    submission_ids = conn.select_all(sql)

    if submission_ids.length > 0
      completed = Status.find_or_create_by_name("Completed");

      for submission_id in submission_ids
        s = Submission.find(submission_id['id'], :include => [:judgments])
        logger.info("completing submission_id: #{s.id}")
        s.status = completed
        s.judges_avg_score = s.judgments.average(:score)
        s.save
      end
    end
  end

  def self.mark_approved
    conn = ActiveRecord::Base.connection()

    # find all of the submissions that are ready to be moved to completed.  If
    # the status is judging and all of the judgement are complete, then they
    # are ready.
    # mysql only!!
    sql = """SELECT s.id
               FROM submissions s 
               JOIN statuses ON (s.status_id = statuses.id)
               LEFT JOIN (SELECT submission_id as id, count(*) as num
                            FROM approvals
                           WHERE complete = 1 
                             and approved = 1
                           GROUP BY submission_id) acc ON (acc.id = s.id)
               LEFT JOIN (SELECT submission_id as id, count(*) as num
                            FROM approvals
                           GROUP BY submission_id) ac ON (ac.id = s.id)
              WHERE statuses.name = 'Awaiting Approval'
                AND acc.num = ac.num"""

    submission_ids = conn.select_all(sql)

    if submission_ids.length > 0
      approved = Status.find_or_create_by_name("Approved");

      for submission_id in submission_ids
        s = Submission.find(submission_id['id'])
        s.status = approved
        s.save

        # I am not sure that the notification of judges is of much use, so I will comment it out.
        #User.find_admins.each { |admin|
        #  UserNotifier.deliver_waiting_judges(admin)
        #}
      end
    end
  end


  def self.mark_not_approved
    conn = ActiveRecord::Base.connection()
    sql = """SELECT distinct s.id
               FROM submissions s 
               JOIN statuses ON (s.status_id = statuses.id)
               JOIN approvals a on (s.id = a.submission_id)
              WHERE statuses.name = 'Awaiting Approval'
                AND complete = 1
                AND approved = 0"""

    submission_ids = conn.select_all(sql)

    if submission_ids.length > 0
      not_approved = Status.find_or_create_by_name("Not Approved");

      for submission_id in submission_ids
        s = Submission.find(submission_id['id'])
        s.status = not_approved
        #UserNotifier.deliver_notify_not_approved(s.user)
        s.save
      end        
    end
  end    
                                
  # Add an approval to the submission
  def add_approval(manager)       
    
    # go through the approvals looking for
    for a in self.approvals
      if a.manager == manager
        return
      end
    end
    
    self.approvals << Approval.create(:manager => manager)
    UserNotifier.deliver_waiting_approval(manager)                                                                           
  end
end
